{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "## Joyful pandas第一次综合练习"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "!pip install pandas==1.2.0 -q\r\n",
    "!unzip data/data66639/123 -d data/ > /dev/null 2>&1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import numpy as np\r\n",
    "import pandas as pd\r\n",
    "import os"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "【任务一】企业收入的多样性\n",
    "\n",
    "【题目描述】一个企业的产业收入多样性可以仿照信息熵的概念来定义收入熵指标：\n",
    "\n",
    "$$ \\rm I=-\\sum_{i}p(x_i)\\log(p(x_i)) $$\n",
    "\n",
    "其中$p(x_i)$是企业该年某产业收入额占该年所有产业总收入的比重。在`company.csv`中存有需要计算的企业和年份，在`company_data.csv`中存有企业、各类收入额和收入年份的信息。现请利用后一张表中的数据，在前一张表中增加一列表示该公司该年份的收入熵指标$I$\n",
    " 。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "思路：\n",
    "\n",
    "首先是看一看数据的特征，company.csv中共有1048行，数据格式是公司代码和时间\n",
    "\n",
    "company_data.csv共有96万行，数据格式是公司代码，统计时间，收入类型和收入。统计过后\n",
    "\n",
    "因此基本思路是从第一个csv中提取出公司和年份,然后在第二个csv中找出对应的公司年份，计算收入多样性，然后将计算出来的数据写入第一张表中。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      证券代码    日期\n",
      "0  #000007  2014\n",
      "1  #000403  2015\n",
      "2  #000408  2016\n",
      "___________________________________________________________________________________\n",
      "(1048, 2)\n",
      "___________________________________________________________________________________\n",
      "(964022, 4)\n",
      "___________________________________________________________________________________\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>证券代码</th>\n",
       "      <th>日期</th>\n",
       "      <th>收入类型</th>\n",
       "      <th>收入额</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2008/12/31</td>\n",
       "      <td>1</td>\n",
       "      <td>1.084218e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2008/12/31</td>\n",
       "      <td>2</td>\n",
       "      <td>1.259789e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>2008/12/31</td>\n",
       "      <td>3</td>\n",
       "      <td>1.451312e+10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   证券代码          日期  收入类型           收入额\n",
       "0     1  2008/12/31     1  1.084218e+10\n",
       "1     1  2008/12/31     2  1.259789e+10\n",
       "2     1  2008/12/31     3  1.451312e+10"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.read_csv('data/1/Company.csv')\r\n",
    "df2 = pd.read_csv('data/1/Company_data.csv')\r\n",
    "print(df1.head(3))\r\n",
    "print('___________________________________________________________________________________')\r\n",
    "print(df1.shape)\r\n",
    "print('___________________________________________________________________________________')\r\n",
    "print(df2.shape)\r\n",
    "print('___________________________________________________________________________________')\r\n",
    "df2.head(3)\r\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "方便表1到表2的时间转化，把表二的年份截出来"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2['日期'] = pd.to_datetime(df2['日期']) \r\n",
    "df2['日期'] = df2['日期'].dt.year "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>收入类型</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>10</th>\n",
       "      <th>...</th>\n",
       "      <th>167</th>\n",
       "      <th>168</th>\n",
       "      <th>169</th>\n",
       "      <th>170</th>\n",
       "      <th>171</th>\n",
       "      <th>172</th>\n",
       "      <th>173</th>\n",
       "      <th>174</th>\n",
       "      <th>175</th>\n",
       "      <th>176</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>证券代码</th>\n",
       "      <th>日期</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">1</th>\n",
       "      <th>2008</th>\n",
       "      <td>1.084218e+10</td>\n",
       "      <td>1.259789e+10</td>\n",
       "      <td>1.451312e+10</td>\n",
       "      <td>1.063843e+09</td>\n",
       "      <td>8.513880e+08</td>\n",
       "      <td>2.062714e+09</td>\n",
       "      <td>-3.070010e+08</td>\n",
       "      <td>9.605849e+09</td>\n",
       "      <td>-8.697750e+08</td>\n",
       "      <td>9.174519e+09</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009</th>\n",
       "      <td>1.451300e+10</td>\n",
       "      <td>1.511400e+10</td>\n",
       "      <td>2.150000e+09</td>\n",
       "      <td>8.280000e+08</td>\n",
       "      <td>7.473000e+09</td>\n",
       "      <td>4.148000e+09</td>\n",
       "      <td>2.604000e+09</td>\n",
       "      <td>8.890000e+08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010</th>\n",
       "      <td>1.802200e+10</td>\n",
       "      <td>1.231984e+10</td>\n",
       "      <td>3.702065e+09</td>\n",
       "      <td>1.169751e+09</td>\n",
       "      <td>8.306220e+08</td>\n",
       "      <td>1.802228e+10</td>\n",
       "      <td>9.460628e+09</td>\n",
       "      <td>3.205930e+09</td>\n",
       "      <td>1.180634e+09</td>\n",
       "      <td>1.267248e+09</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011</th>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.675391e+09</td>\n",
       "      <td>4.209000e+09</td>\n",
       "      <td>1.156000e+07</td>\n",
       "      <td>2.257000e+09</td>\n",
       "      <td>4.731454e+09</td>\n",
       "      <td>7.801170e+08</td>\n",
       "      <td>1.868284e+10</td>\n",
       "      <td>4.043000e+09</td>\n",
       "      <td>1.231984e+10</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012</th>\n",
       "      <td>3.974865e+10</td>\n",
       "      <td>1.102043e+10</td>\n",
       "      <td>1.868284e+10</td>\n",
       "      <td>5.056154e+09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.156000e+07</td>\n",
       "      <td>8.610000e+09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.964300e+10</td>\n",
       "      <td>2.257000e+09</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 176 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "收入类型                1             2             3             4    \\\n",
       "证券代码 日期                                                             \n",
       "1    2008  1.084218e+10  1.259789e+10  1.451312e+10  1.063843e+09   \n",
       "     2009  1.451300e+10  1.511400e+10  2.150000e+09  8.280000e+08   \n",
       "     2010  1.802200e+10  1.231984e+10  3.702065e+09  1.169751e+09   \n",
       "     2011           NaN -1.675391e+09  4.209000e+09  1.156000e+07   \n",
       "     2012  3.974865e+10  1.102043e+10  1.868284e+10  5.056154e+09   \n",
       "\n",
       "收入类型                5             6             7             8    \\\n",
       "证券代码 日期                                                             \n",
       "1    2008  8.513880e+08  2.062714e+09 -3.070010e+08  9.605849e+09   \n",
       "     2009  7.473000e+09  4.148000e+09  2.604000e+09  8.890000e+08   \n",
       "     2010  8.306220e+08  1.802228e+10  9.460628e+09  3.205930e+09   \n",
       "     2011  2.257000e+09  4.731454e+09  7.801170e+08  1.868284e+10   \n",
       "     2012           NaN  1.156000e+07  8.610000e+09           NaN   \n",
       "\n",
       "收入类型                9             10   ...  167  168  169  170  171  172  173  \\\n",
       "证券代码 日期                                ...                                      \n",
       "1    2008 -8.697750e+08  9.174519e+09  ...  NaN  NaN  NaN  NaN  NaN  NaN  NaN   \n",
       "     2009           NaN           NaN  ...  NaN  NaN  NaN  NaN  NaN  NaN  NaN   \n",
       "     2010  1.180634e+09  1.267248e+09  ...  NaN  NaN  NaN  NaN  NaN  NaN  NaN   \n",
       "     2011  4.043000e+09  1.231984e+10  ...  NaN  NaN  NaN  NaN  NaN  NaN  NaN   \n",
       "     2012  2.964300e+10  2.257000e+09  ...  NaN  NaN  NaN  NaN  NaN  NaN  NaN   \n",
       "\n",
       "收入类型       174  175  176  \n",
       "证券代码 日期                   \n",
       "1    2008  NaN  NaN  NaN  \n",
       "     2009  NaN  NaN  NaN  \n",
       "     2010  NaN  NaN  NaN  \n",
       "     2011  NaN  NaN  NaN  \n",
       "     2012  NaN  NaN  NaN  \n",
       "\n",
       "[5 rows x 176 columns]"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#\r\n",
    "df3 = df2.pivot(index=['证券代码','日期'],columns='收入类型',values='收入额')\r\n",
    "df3.head()\r\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>收入类型</th>\n",
       "      <th>证券代码</th>\n",
       "      <th>日期</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>...</th>\n",
       "      <th>167</th>\n",
       "      <th>168</th>\n",
       "      <th>169</th>\n",
       "      <th>170</th>\n",
       "      <th>171</th>\n",
       "      <th>172</th>\n",
       "      <th>173</th>\n",
       "      <th>174</th>\n",
       "      <th>175</th>\n",
       "      <th>176</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2008</td>\n",
       "      <td>1.084218e+10</td>\n",
       "      <td>1.259789e+10</td>\n",
       "      <td>1.451312e+10</td>\n",
       "      <td>1.063843e+09</td>\n",
       "      <td>8.513880e+08</td>\n",
       "      <td>2.062714e+09</td>\n",
       "      <td>-3.070010e+08</td>\n",
       "      <td>9.605849e+09</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2009</td>\n",
       "      <td>1.451300e+10</td>\n",
       "      <td>1.511400e+10</td>\n",
       "      <td>2.150000e+09</td>\n",
       "      <td>8.280000e+08</td>\n",
       "      <td>7.473000e+09</td>\n",
       "      <td>4.148000e+09</td>\n",
       "      <td>2.604000e+09</td>\n",
       "      <td>8.890000e+08</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>2010</td>\n",
       "      <td>1.802200e+10</td>\n",
       "      <td>1.231984e+10</td>\n",
       "      <td>3.702065e+09</td>\n",
       "      <td>1.169751e+09</td>\n",
       "      <td>8.306220e+08</td>\n",
       "      <td>1.802228e+10</td>\n",
       "      <td>9.460628e+09</td>\n",
       "      <td>3.205930e+09</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>2011</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.675391e+09</td>\n",
       "      <td>4.209000e+09</td>\n",
       "      <td>1.156000e+07</td>\n",
       "      <td>2.257000e+09</td>\n",
       "      <td>4.731454e+09</td>\n",
       "      <td>7.801170e+08</td>\n",
       "      <td>1.868284e+10</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>2012</td>\n",
       "      <td>3.974865e+10</td>\n",
       "      <td>1.102043e+10</td>\n",
       "      <td>1.868284e+10</td>\n",
       "      <td>5.056154e+09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.156000e+07</td>\n",
       "      <td>8.610000e+09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21453</th>\n",
       "      <td>900957</td>\n",
       "      <td>2012</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.018734e+06</td>\n",
       "      <td>1.059993e+07</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.153846e+06</td>\n",
       "      <td>1.059993e+07</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21454</th>\n",
       "      <td>900957</td>\n",
       "      <td>2013</td>\n",
       "      <td>1.134371e+07</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.097214e+06</td>\n",
       "      <td>9.246496e+06</td>\n",
       "      <td>1.134371e+07</td>\n",
       "      <td>2.097214e+06</td>\n",
       "      <td>9.246496e+06</td>\n",
       "      <td>1.134371e+07</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21455</th>\n",
       "      <td>900957</td>\n",
       "      <td>2014</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>1.134371e+07</td>\n",
       "      <td>4.448220e+06</td>\n",
       "      <td>1.134371e+07</td>\n",
       "      <td>8.544888e+06</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>1.830678e+07</td>\n",
       "      <td>1.299311e+07</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21456</th>\n",
       "      <td>900957</td>\n",
       "      <td>2015</td>\n",
       "      <td>4.185460e+06</td>\n",
       "      <td>4.788678e+07</td>\n",
       "      <td>5.207224e+07</td>\n",
       "      <td>4.185460e+06</td>\n",
       "      <td>4.788678e+07</td>\n",
       "      <td>5.207224e+07</td>\n",
       "      <td>4.185460e+06</td>\n",
       "      <td>4.788678e+07</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21457</th>\n",
       "      <td>900957</td>\n",
       "      <td>2016</td>\n",
       "      <td>8.062995e+07</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>8.062995e+07</td>\n",
       "      <td>8.062995e+07</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>8.062995e+07</td>\n",
       "      <td>8.062995e+07</td>\n",
       "      <td>8.062995e+07</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>21458 rows × 178 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "收入类型     证券代码    日期             1             2             3             4  \\\n",
       "0           1  2008  1.084218e+10  1.259789e+10  1.451312e+10  1.063843e+09   \n",
       "1           1  2009  1.451300e+10  1.511400e+10  2.150000e+09  8.280000e+08   \n",
       "2           1  2010  1.802200e+10  1.231984e+10  3.702065e+09  1.169751e+09   \n",
       "3           1  2011           NaN -1.675391e+09  4.209000e+09  1.156000e+07   \n",
       "4           1  2012  3.974865e+10  1.102043e+10  1.868284e+10  5.056154e+09   \n",
       "...       ...   ...           ...           ...           ...           ...   \n",
       "21453  900957  2012           NaN           NaN  6.018734e+06  1.059993e+07   \n",
       "21454  900957  2013  1.134371e+07           NaN  2.097214e+06  9.246496e+06   \n",
       "21455  900957  2014  0.000000e+00  1.134371e+07  4.448220e+06  1.134371e+07   \n",
       "21456  900957  2015  4.185460e+06  4.788678e+07  5.207224e+07  4.185460e+06   \n",
       "21457  900957  2016  8.062995e+07  0.000000e+00  8.062995e+07  8.062995e+07   \n",
       "\n",
       "收入类型              5             6             7             8  ...  167  168  \\\n",
       "0      8.513880e+08  2.062714e+09 -3.070010e+08  9.605849e+09  ...  NaN  NaN   \n",
       "1      7.473000e+09  4.148000e+09  2.604000e+09  8.890000e+08  ...  NaN  NaN   \n",
       "2      8.306220e+08  1.802228e+10  9.460628e+09  3.205930e+09  ...  NaN  NaN   \n",
       "3      2.257000e+09  4.731454e+09  7.801170e+08  1.868284e+10  ...  NaN  NaN   \n",
       "4               NaN  1.156000e+07  8.610000e+09           NaN  ...  NaN  NaN   \n",
       "...             ...           ...           ...           ...  ...  ...  ...   \n",
       "21453           NaN           NaN  1.153846e+06  1.059993e+07  ...  NaN  NaN   \n",
       "21454  1.134371e+07  2.097214e+06  9.246496e+06  1.134371e+07  ...  NaN  NaN   \n",
       "21455  8.544888e+06  0.000000e+00  1.830678e+07  1.299311e+07  ...  NaN  NaN   \n",
       "21456  4.788678e+07  5.207224e+07  4.185460e+06  4.788678e+07  ...  NaN  NaN   \n",
       "21457  0.000000e+00  8.062995e+07  8.062995e+07  8.062995e+07  ...  NaN  NaN   \n",
       "\n",
       "收入类型   169  170  171  172  173  174  175  176  \n",
       "0      NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN  \n",
       "1      NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN  \n",
       "2      NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN  \n",
       "3      NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN  \n",
       "4      NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN  \n",
       "...    ...  ...  ...  ...  ...  ...  ...  ...  \n",
       "21453  NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN  \n",
       "21454  NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN  \n",
       "21455  NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN  \n",
       "21456  NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN  \n",
       "21457  NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN  \n",
       "\n",
       "[21458 rows x 178 columns]"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3 = df3.reset_index()\r\n",
    "df3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "求出单年的收入总和\n",
    "\n",
    "这里就出了问题。。。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df4 = df3.groupby(['证券代码', '日期', '收入类型'])['收入额'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def eye(x):\r\n",
    "    px = x/(x.sum())\r\n",
    "    res = (px.sum)*(np.log(px)).sum\r\n",
    "    return res\r\n",
    "\r\n",
    "df3['收入类型'].apply(eye)\r\n",
    "#这个不对，大大的不对"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "【任务二】组队学习信息表的变换\n",
    "\n",
    "【题目描述】请把组队学习的队伍信息表变换为如下形态，其中“是否队长”一列取1表示队长，否则为0\n",
    "\n",
    "```\n",
    "\t是否队长\t队伍名称\t    昵称    \t编号\n",
    "0\t1\t    你说的都对队\t山枫叶纷飞\t5\n",
    "1\t0\t    你说的都对队\t蔡\t        6\n",
    "2\t0\t    你说的都对队\t安慕希\t    7\n",
    "3\t0\t    你说的都对队\t信仰\t    8\n",
    "4\t0\t    你说的都对队\tbiubiu🙈🙈\t20\n",
    "...\t...\t    ...\t        ...\t        ...\n",
    "141\t0\t    七星联盟\t    Daisy\t    63\n",
    "142\t0\t    七星联盟    \tOne Better\t131\n",
    "143\t0\t    七星联盟    \train\t    112\n",
    "144\t1\t    应如是\t    思无邪\t    54\n",
    "145\t0\t    应如是\t    Justzer0\t58\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "思路：\n",
    "\n",
    "首先查看一下表格的形状,共有22行24列，其中有一整行都是NaN，需要删去\n",
    "\n",
    "提供的数据是宽表，总体思路是把宽表转换为长表;\n",
    "\n",
    "为了方便转换，需要把队长昵称队员昵称全部改成昵称\n",
    "\n",
    "需要把是队长的同学的行加上1，其他的加上0，也就是提取出原宽表的队长编号这一列，给这些编号的同学所在行加上1，其余都加上2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(22, 24)\n",
      "___________________________________________________________________________________\n"
     ]
    }
   ],
   "source": [
    "df1 = pd.read_excel('data/2/组队信息汇总表（Pandas）.xlsx')\r\n",
    "print(df1.shape)\r\n",
    "print('___________________________________________________________________________________')\r\n",
    "s1 = df1['队长编号']\r\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>所在群</th>\n",
       "      <th>队伍名称</th>\n",
       "      <th>队长编号</th>\n",
       "      <th>队长_群昵称</th>\n",
       "      <th>队员1 编号</th>\n",
       "      <th>队员_群昵称</th>\n",
       "      <th>队员2 编号</th>\n",
       "      <th>队员_群昵称.1</th>\n",
       "      <th>队员3 编号</th>\n",
       "      <th>队员_群昵称.2</th>\n",
       "      <th>...</th>\n",
       "      <th>队员6 编号</th>\n",
       "      <th>队员_群昵称.5</th>\n",
       "      <th>队员7 编号</th>\n",
       "      <th>队员_群昵称.6</th>\n",
       "      <th>队员8 编号</th>\n",
       "      <th>队员_群昵称.7</th>\n",
       "      <th>队员9 编号</th>\n",
       "      <th>队员_群昵称.8</th>\n",
       "      <th>队员10编号</th>\n",
       "      <th>队员_群昵称.9</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>...</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>...</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>...</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>...</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>22 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      所在群   队伍名称   队长编号  队长_群昵称  队员1 编号  队员_群昵称  队员2 编号  队员_群昵称.1  队员3 编号  \\\n",
       "0   False  False  False   False   False   False   False     False   False   \n",
       "1   False  False  False   False   False   False   False     False   False   \n",
       "2   False  False  False   False   False   False   False     False   False   \n",
       "3   False  False  False   False   False   False   False     False   False   \n",
       "4   False  False  False   False   False   False   False     False   False   \n",
       "5   False  False  False   False   False   False   False     False   False   \n",
       "6   False  False  False   False   False   False   False     False   False   \n",
       "7   False  False  False   False   False   False   False     False   False   \n",
       "8   False  False  False   False   False   False   False     False    True   \n",
       "9   False  False  False   False   False   False   False     False   False   \n",
       "10  False  False  False   False   False   False   False     False   False   \n",
       "11  False  False  False   False   False   False   False     False   False   \n",
       "12  False  False  False   False   False   False   False     False   False   \n",
       "13  False  False  False   False   False   False   False     False    True   \n",
       "14  False  False  False   False   False   False   False     False   False   \n",
       "15  False  False  False   False   False   False   False     False   False   \n",
       "16  False  False  False   False   False   False   False     False   False   \n",
       "17  False  False  False   False   False   False   False     False   False   \n",
       "18  False  False  False   False   False   False   False     False   False   \n",
       "19  False  False  False   False   False   False   False     False   False   \n",
       "20  False  False  False   False   False   False    True      True    True   \n",
       "21   True   True   True    True    True    True    True      True    True   \n",
       "\n",
       "    队员_群昵称.2  ...  队员6 编号  队员_群昵称.5  队员7 编号  队员_群昵称.6  队员8 编号  队员_群昵称.7  \\\n",
       "0      False  ...    True      True    True      True    True      True   \n",
       "1      False  ...   False     False   False     False   False     False   \n",
       "2      False  ...    True      True    True      True    True      True   \n",
       "3      False  ...   False     False   False     False   False     False   \n",
       "4      False  ...   False     False   False     False   False     False   \n",
       "5      False  ...    True      True    True      True    True      True   \n",
       "6      False  ...   False     False   False     False   False     False   \n",
       "7      False  ...   False     False   False     False   False     False   \n",
       "8       True  ...    True      True    True      True    True      True   \n",
       "9      False  ...   False     False   False     False    True      True   \n",
       "10     False  ...   False     False    True      True    True      True   \n",
       "11     False  ...   False     False   False     False   False     False   \n",
       "12     False  ...   False     False   False     False   False     False   \n",
       "13      True  ...    True      True    True      True    True      True   \n",
       "14     False  ...    True      True    True      True    True      True   \n",
       "15     False  ...   False     False   False     False   False     False   \n",
       "16     False  ...   False     False    True      True    True      True   \n",
       "17     False  ...    True      True    True      True    True      True   \n",
       "18     False  ...   False     False   False     False    True      True   \n",
       "19     False  ...   False     False    True      True    True      True   \n",
       "20      True  ...    True      True    True      True    True      True   \n",
       "21      True  ...    True      True    True      True    True      True   \n",
       "\n",
       "    队员9 编号  队员_群昵称.8  队员10编号  队员_群昵称.9  \n",
       "0     True      True    True      True  \n",
       "1    False     False   False     False  \n",
       "2     True      True    True      True  \n",
       "3     True      True    True      True  \n",
       "4     True      True    True      True  \n",
       "5     True      True    True      True  \n",
       "6    False     False    True      True  \n",
       "7    False     False    True      True  \n",
       "8     True      True    True      True  \n",
       "9     True      True    True      True  \n",
       "10    True      True    True      True  \n",
       "11    True      True    True      True  \n",
       "12   False     False    True      True  \n",
       "13    True      True    True      True  \n",
       "14    True      True    True      True  \n",
       "15    True      True    True      True  \n",
       "16    True      True    True      True  \n",
       "17    True      True    True      True  \n",
       "18    True      True    True      True  \n",
       "19    True      True    True      True  \n",
       "20    True      True    True      True  \n",
       "21    True      True    True      True  \n",
       "\n",
       "[22 rows x 24 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.isna()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "最终形式中没有所在群，所以删去\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>队伍名称</th>\n",
       "      <th>队长编号</th>\n",
       "      <th>队长_群昵称</th>\n",
       "      <th>队员1 编号</th>\n",
       "      <th>队员_群昵称</th>\n",
       "      <th>队员2 编号</th>\n",
       "      <th>队员_群昵称.1</th>\n",
       "      <th>队员3 编号</th>\n",
       "      <th>队员_群昵称.2</th>\n",
       "      <th>队员4 编号</th>\n",
       "      <th>...</th>\n",
       "      <th>队员6 编号</th>\n",
       "      <th>队员_群昵称.5</th>\n",
       "      <th>队员7 编号</th>\n",
       "      <th>队员_群昵称.6</th>\n",
       "      <th>队员8 编号</th>\n",
       "      <th>队员_群昵称.7</th>\n",
       "      <th>队员9 编号</th>\n",
       "      <th>队员_群昵称.8</th>\n",
       "      <th>队员10编号</th>\n",
       "      <th>队员_群昵称.9</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>你说的都对队</td>\n",
       "      <td>5.0</td>\n",
       "      <td>山枫叶纷飞</td>\n",
       "      <td>6.0</td>\n",
       "      <td>蔡</td>\n",
       "      <td>7.0</td>\n",
       "      <td>安慕希</td>\n",
       "      <td>8.0</td>\n",
       "      <td>信仰</td>\n",
       "      <td>20.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>熊猫人</td>\n",
       "      <td>175.0</td>\n",
       "      <td>鱼呲呲</td>\n",
       "      <td>44.0</td>\n",
       "      <td>Heaven</td>\n",
       "      <td>37.0</td>\n",
       "      <td>吕青</td>\n",
       "      <td>50.0</td>\n",
       "      <td>余柳成荫</td>\n",
       "      <td>82.0</td>\n",
       "      <td>...</td>\n",
       "      <td>25.0</td>\n",
       "      <td>Never say never</td>\n",
       "      <td>55.0</td>\n",
       "      <td>K</td>\n",
       "      <td>120.0</td>\n",
       "      <td>Y.</td>\n",
       "      <td>28.0</td>\n",
       "      <td>X.Y.Q</td>\n",
       "      <td>151.0</td>\n",
       "      <td>swrong</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>中国移不动</td>\n",
       "      <td>107.0</td>\n",
       "      <td>Y's</td>\n",
       "      <td>124.0</td>\n",
       "      <td>🥕</td>\n",
       "      <td>75.0</td>\n",
       "      <td>Vito</td>\n",
       "      <td>146.0</td>\n",
       "      <td>张小五</td>\n",
       "      <td>186.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>panda</td>\n",
       "      <td>11.0</td>\n",
       "      <td>太下真君</td>\n",
       "      <td>35.0</td>\n",
       "      <td>柚子</td>\n",
       "      <td>108.0</td>\n",
       "      <td>My</td>\n",
       "      <td>42.0</td>\n",
       "      <td>星星点灯</td>\n",
       "      <td>45.0</td>\n",
       "      <td>...</td>\n",
       "      <td>157.0</td>\n",
       "      <td>Zys</td>\n",
       "      <td>158.0</td>\n",
       "      <td>不器</td>\n",
       "      <td>102.0</td>\n",
       "      <td>嘉平佑染</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>一路向北</td>\n",
       "      <td>13.0</td>\n",
       "      <td>黄元帅</td>\n",
       "      <td>15.0</td>\n",
       "      <td>化</td>\n",
       "      <td>16.0</td>\n",
       "      <td>未期</td>\n",
       "      <td>18.0</td>\n",
       "      <td>太陽光下</td>\n",
       "      <td>19.0</td>\n",
       "      <td>...</td>\n",
       "      <td>23.0</td>\n",
       "      <td>🚀</td>\n",
       "      <td>169.0</td>\n",
       "      <td>听风</td>\n",
       "      <td>189.0</td>\n",
       "      <td>Cappuccino</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     队伍名称   队长编号  队长_群昵称  队员1 编号  队员_群昵称  队员2 编号 队员_群昵称.1  队员3 编号 队员_群昵称.2  \\\n",
       "0  你说的都对队    5.0  山枫叶纷飞      6.0      蔡      7.0     安慕希      8.0       信仰   \n",
       "1     熊猫人  175.0     鱼呲呲    44.0  Heaven    37.0       吕青    50.0     余柳成荫   \n",
       "2   中国移不动  107.0     Y's   124.0       🥕    75.0     Vito   146.0      张小五   \n",
       "3   panda   11.0    太下真君    35.0      柚子   108.0       My    42.0     星星点灯   \n",
       "4    一路向北   13.0     黄元帅    15.0       化    16.0       未期    18.0     太陽光下   \n",
       "\n",
       "   队员4 编号  ... 队员6 编号         队员_群昵称.5 队员7 编号  队员_群昵称.6 队员8 编号    队员_群昵称.7  \\\n",
       "0    20.0  ...    NaN              NaN    NaN       NaN    NaN         NaN   \n",
       "1    82.0  ...   25.0  Never say never   55.0         K  120.0          Y.   \n",
       "2   186.0  ...    NaN              NaN    NaN       NaN    NaN         NaN   \n",
       "3    45.0  ...  157.0              Zys  158.0        不器  102.0        嘉平佑染   \n",
       "4    19.0  ...   23.0                🚀  169.0        听风  189.0  Cappuccino   \n",
       "\n",
       "  队员9 编号  队员_群昵称.8 队员10编号  队员_群昵称.9  \n",
       "0    NaN       NaN    NaN       NaN  \n",
       "1   28.0     X.Y.Q  151.0    swrong  \n",
       "2    NaN       NaN    NaN       NaN  \n",
       "3    NaN       NaN    NaN       NaN  \n",
       "4    NaN       NaN    NaN       NaN  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = df1.drop(columns='所在群')\r\n",
    "df1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "为了方便后面转换，重命名列名"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df2 = .rename(columns={'队长编号': '编号_队长', '队员1 编号': '编号_队员1','队员2 编号': '编号_队员2', \r\n",
    "                        '队员3 编号': '编号_队员3','队员4 编号': '编号_队员4', '队员5 编号': '编号_队员5',\r\n",
    "                        '队员6 编号': '编号_队员6', '队员7 编号': '编号_队员7','队员8 编号': '编号_队员8', \r\n",
    "                        '队员9 编号': '编号_队员9','队员10编号': '编号_队员10', \r\n",
    "                        '队长_群昵称':'昵称_队长','队员_群昵称': '昵称_队员1', '队员_群昵称1': '昵称_队员2',\r\n",
    "                        '队员_群昵称2': '昵称_队员3', '队员_群昵称3': '昵称_队员4','队员_群昵称4': '昵称_队员5',\r\n",
    "                         '队员_群昵称5': '昵称_队员6','队员_群昵称6': '昵称_队员7', '队员_群昵称7': '昵称_队员8',\r\n",
    "                        '队员_群昵称8': '昵称_队员9', '队员_群昵称9': '昵称_队员10'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#df1.melt(id_vars=['昵称','编号'],value_vars = '队伍名称', var_name = '队伍名称', value_name = ))\r\n",
    "pd.wide_to_long(df1,  stubnames=['昵称', '编号'],i = ['index', '队伍名称'],j='是否队长',sep='_',suffix='.+')        "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "【任务三】美国大选投票情况\n",
    "\n",
    "【题目描述】两张数据表中分别给出了美国各县（county）的人口数以及大选的投票情况，请解决以下问题：\n",
    "\n",
    "有多少县满足总投票数超过县人口数的一半  \n",
    "把州（state）作为行索引，把投票候选人作为列名，列名的顺序按照候选人在全美的总票数由高到低排序，行列对应的元素为该候选人在该州获得的总票数\n",
    "```\n",
    "# 此处是一个样例，实际的州或人名用原表的英语代替\n",
    "            拜登   川普\n",
    "威斯康星州   2      1\n",
    "德克萨斯州   3      4\n",
    "```\n",
    "每一个州下设若干县，定义拜登在该县的得票率减去川普在该县的得票率为该县的BT指标，若某个州所有县BT指标的中位数大于0，则称该州为Biden State，请找出所有的Biden State"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "思路：\n",
    "\n",
    "county_population.csv中给出了每个州中的每个县的人口，州和县在一起；president_county_candidate.csv中则给出了每个州中每个县四位候选人的得票情况\n",
    "\n",
    "超过county人口数一半：首先需要把df2中每个县的数据加起来，然后与df1中的总人口数作比较\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1 = pd.read_csv('data/3/county_population.csv')\r\n",
    "df2 = pd.read_csv('data/3/president_county_candidate.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>state</th>\n",
       "      <th>county</th>\n",
       "      <th>candidate</th>\n",
       "      <th>party</th>\n",
       "      <th>total_votes</th>\n",
       "      <th>won</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Delaware</td>\n",
       "      <td>Kent County</td>\n",
       "      <td>Joe Biden</td>\n",
       "      <td>DEM</td>\n",
       "      <td>44552</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Delaware</td>\n",
       "      <td>Kent County</td>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>REP</td>\n",
       "      <td>41009</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Delaware</td>\n",
       "      <td>Kent County</td>\n",
       "      <td>Jo Jorgensen</td>\n",
       "      <td>LIB</td>\n",
       "      <td>1044</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Delaware</td>\n",
       "      <td>Kent County</td>\n",
       "      <td>Howie Hawkins</td>\n",
       "      <td>GRN</td>\n",
       "      <td>420</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Delaware</td>\n",
       "      <td>New Castle County</td>\n",
       "      <td>Joe Biden</td>\n",
       "      <td>DEM</td>\n",
       "      <td>195034</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      state             county      candidate party  total_votes    won\n",
       "0  Delaware        Kent County      Joe Biden   DEM        44552   True\n",
       "1  Delaware        Kent County   Donald Trump   REP        41009  False\n",
       "2  Delaware        Kent County   Jo Jorgensen   LIB         1044  False\n",
       "3  Delaware        Kent County  Howie Hawkins   GRN          420  False\n",
       "4  Delaware  New Castle County      Joe Biden   DEM       195034   True"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "首先 统计出来每个县的总投票数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "county\n",
       "Abbeville County    12433\n",
       "Abbot                 417\n",
       "Abington             9660\n",
       "Acadia Parish       28425\n",
       "Accomack County     16962\n",
       "                    ...  \n",
       "Yuba County         29787\n",
       "Yuma County         74907\n",
       "Zapata County        3874\n",
       "Zavala County        4379\n",
       "Ziebach County        906\n",
       "Name: total_votes, Length: 3007, dtype: int64"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.groupby('county')['total_votes'].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "## 上面都做的乱七八糟的，有的有了思路却找不到合适的函数\n",
    "\n",
    "## 2021年1月2日更新：观摩参考答案思路"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "第一题\n",
    "\n",
    "首先把df2的证券代码补全成六位  \n",
    "`df2['证券代码'] = df2['证券代码'].apply(lambda x:'#%06d'%x)`  \n",
    "(我自己的思路好像是反过来的。。。）  \n",
    "\n",
    "找出df2中需要的数据\n",
    "`df2 = df2[df2['证券代码'].isin(df1['证券代码'])]`\n",
    "\n",
    "把df2中的日期取成年份  \n",
    "`df2['日期'] = df2['日期'].apply(lambda x: int(x[:4]))`  \n",
    "（我的方法日期格式转来转去也行）\n",
    "\n",
    "求信息熵  \n",
    "`res = df2.groupby(['证券代码', '日期'])['收入额'].apply(lambda x: -((x/x.sum()*np.log(x/x.sum()))).sum()).reset_index()`\n",
    "\n",
    "做出结果  \n",
    "`res = df1.merge(res, how='left', on=['证券代码', '日期']).rename(columns={'收入额': '收入熵'})`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "第二题\n",
    "\n",
    "首先排出来一个转置的队伍名称为索引的表  \n",
    "`temp = df.iloc[:,1::2].set_index('队伍名称').T.reset_index(drop=True)`  \n",
    "\n",
    "然后根据是不是队长拼接出一个队长所在行是1，其他都是0的列  \n",
    "`temp['是否队长'] = np.r_[[1], np.zeros(temp.shape[0]-1)].astype('int')`  \n",
    "\n",
    "做一个宽表转长表的操作,drop=True是把原本索引的一行去掉了  \n",
    "`melted = temp.melt(id_vars = '是否队长', value_vars = temp.columns[:-1], var_name = '队伍名称', value_name = '昵称').dropna().reset_index(drop=True)`  \n",
    "\n",
    "把原始数据中的编号昵称对应关系提取出来；提取的时候是从第2行、4行逐个提取的  \n",
    "`number = pd.concat([df.iloc[:, 2*(i+1): 2*(i+2)].T.reset_index(drop=True).T for i in range(11)]).rename({0:'编号', 1:'昵称'}, axis=1).dropna().reset_index(drop=True)`  \n",
    "\n",
    "把上面两个中间结果粘起来  \n",
    "`res = melted.merge(number, how='left', on='昵称')`  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "第三题\n",
    "\n",
    "读取数据\n",
    "```\n",
    "df = pd.read_csv('president_county_candidate.csv')\n",
    "df_pop = pd.read_csv('county_population.csv')\n",
    "```\n",
    "\n",
    "把州和县分开\n",
    "```\n",
    "temp = df_pop['US County'].copy()\n",
    "df_pop['state'] = temp.apply(lambda x:x.split(', ')[1])\n",
    "df_pop['county'] = temp.apply(lambda x:x.split(', ')[0][1:])\n",
    "```\n",
    "\n",
    "去掉原有的县  \n",
    "`df_pop = df_pop.drop(['US County'],axis=1)`\n",
    "\n",
    "粘上表一和表二的县  \n",
    "\n",
    "`df = df.merge(df_pop, on=['state','county'],how='left')`\n",
    "\n",
    "求出结果  \n",
    "```\n",
    "df['pop_rate'] = df['total_votes']/df['Population']\n",
    "res = df.groupby(['state','county'])['pop_rate'].agg(lambda x:x.sum())\n",
    "(res>0.5).sum()\n",
    "```\n",
    "\n",
    "长宽表转换\n",
    "`res = df.pivot_table(index='state',columns='candidate',values='total_votes',aggfunc='sum').reindex(df.groupby('candidate')['total_votes'].sum().sort_values(ascending=False).index,axis=1)`\n",
    "\n",
    "转换后不变的是州，新的索引是候选人，使用的值是票数，求和操作（县）；然后再以候选人为一句，求出总和的票（州）\n",
    "\n",
    "求出Biden State\n",
    "```\n",
    "def select(x):\n",
    "    def inner_select(inner_x):\n",
    "        Total = inner_x.total_votes.sum()\n",
    "        Biden = inner_x.query('candidate==\"Joe Biden\"').total_votes.sum()\n",
    "        Trump = inner_x.query('candidate==\"Donald Trump\"').total_votes.sum()\n",
    "        return (Biden-Trump)/Total\n",
    "    res = x.groupby('county')[['candidate','total_votes']].apply(inner_select)\n",
    "    return res.median() > 0\n",
    "```\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "PaddlePaddle 2.0.0b0 (Python 3.5)",
   "language": "python",
   "name": "py35-paddle1.2.0"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
